﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text.RegularExpressions;

namespace SQLServerHealthCheck.SQLServer
{
   public static class SQLHelper
    {
        private static Regex _binaryConstant = new Regex("0X([0-9ABCDEF])+", RegexOptions.Compiled);
        private static Regex _blockComment = new Regex(@"/\*.*?\*/", RegexOptions.CultureInvariant | RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline | RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static Regex _blockCommentDelimiters = new Regex(@"/\*|\*/", RegexOptions.CultureInvariant | RegexOptions.IgnorePatternWhitespace | RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static Regex _brackets = new Regex(@"(\[|\])", RegexOptions.Compiled);
        private static Regex _cursor = new Regex(@"EXEC\s+SP_CURSOROPEN\s+(@CURSOR\s*=\s*)?\@P1\s+OUTPUT\,\s*(@STMT\s*=\s*)?(N)?(?<tick>')  (?<statement>      ((  (?!\k<tick>)  .|\k<tick>{2})*)   )    \k<tick>", RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline | RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static Regex _cursorPrepExec = new Regex("EXEC\r\n\\s+     # any spaces\r\nsp_cursorprepexec\r\n.+       # any characters up to the string\r\nN  \r\n(?<tick>')   # matches an apostraphe\r\n(?!@)    # but no @ following\r\n(?<statement>   ((  (?!\\k<tick>)  .|\\k<tick>{2})*)   )    # all the characters ...\r\n\\k<tick>   # until the next tick that isn't doubled.", RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline | RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static Regex _dbAndObjectName = new Regex(@"EXEC(UTE){0,1}\s+(?<database>\w+)\.\.(?<object>\w+)", RegexOptions.Singleline | RegexOptions.Compiled);
        private static Regex _delimiterStart = new Regex(@"(--)|(/\*)|'", RegexOptions.Compiled);
        private static Regex _doubleApostrophe = new Regex("('')(?<string>.*?)('')", RegexOptions.CultureInvariant | RegexOptions.IgnorePatternWhitespace | RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static Regex _emptyString = new Regex(@"\'\'", RegexOptions.Compiled);
        private static Regex _execPrepped = new Regex(@"^EXEC\s+SP_EXECUTE\s+(?<stmtnum>\d+)", RegexOptions.Compiled);
        private static Regex _execUnprep = new Regex(@"EXEC\s+SP_UNPREPARE\s+(?<stmtnum>\d+)", RegexOptions.Compiled);
        private static Regex _inClause = new Regex(@"IN\s*\(\s*\{.*\}\s*\)", RegexOptions.Singleline | RegexOptions.Compiled);
        private static Regex _inlineComment = new Regex("--.*$", RegexOptions.Compiled | RegexOptions.Multiline);
        private static Regex _numericConstant = new Regex(@"(?<prefix>[\(\s,=\-><\!\&\|\+\*\/\%\~\$])(?<digits>[\-\.\d]+)", RegexOptions.Compiled);
        private static Regex _objectName = new Regex(@"EXEC(UTE){0,1}\s(?<schema>(\w+\.)*)(?<object>\w+)", RegexOptions.Singleline | RegexOptions.Compiled);
        private static Regex _prepareSql = new Regex(@"SET\s+@P1=(?<stmtnum>(NULL|\d+))\s+EXEC\s+(?<preptype>SP_PREP(ARE|EXEC))\s+@P1\s+OUTPUT,\s*(NULL|(N\'.+?\')),\s*N(?<remaining>.+)$", RegexOptions.Singleline | RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static Regex _prepExecRpc = new Regex(@"SET\s+@P1=(?<stmtnum>\d+)\s+EXEC\s+SP_PREPEXECRPC\s+@P1\s+OUTPUT,\s*N\'(?<statement>.+?)'", RegexOptions.Singleline | RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static Regex _preppedSqlStatement = new Regex(@"^(')(?<statement>((?!\1).|\1{2})*)\1", RegexOptions.Singleline | RegexOptions.Compiled);
        private static Regex _spaces = new Regex(@"\s+", RegexOptions.Compiled);
        private static Regex _spExecuteSql = new Regex(@"EXEC\s+SP_EXECUTESQL\s+N\'(?<statement>.+?)\'", RegexOptions.Singleline | RegexOptions.Compiled);
        private static Regex _spreadCsv = new Regex(@",(?=\S)", RegexOptions.Compiled);
        private static Regex _stringConstant = new Regex(@"(')(((?!\1).|\1{2})*)\1", RegexOptions.Singleline | RegexOptions.Compiled);
        private static Regex _unicodeConstant = new Regex("N{STR}", RegexOptions.Compiled);
        private const int CURRENT_DATABASE_VERSION = 2;
        private static Hashtable prepSql = new Hashtable();

        private static string FixComments(string sql)
        {
            string str = sql;
            int num = 0;
            int startat = 0;
            Match match = _delimiterStart.Match(sql, startat);
            while (match.Success)
            {
                string str4 = match.Value;
                if (str4 == null)
                {
                    return sql;
                }
                if (!(str4 == "'"))
                {
                    if (str4 != "--")
                    {
                        if (str4 != "/*")
                        {
                            return sql;
                        }
                        goto Label_00C7;
                    }
                }
                else
                {
                    Match match2 = _stringConstant.Match(sql, match.Index);
                    if (match2.Success)
                    {
                        startat = match.Index + match2.Length;
                    }
                    else
                    {
                        startat++;
                    }
                    goto Label_00E3;
                }
                startat = match.Index;
                if (_inlineComment.Match(sql, startat).Success)
                {
                    sql = _inlineComment.Replace(sql, "", 1, startat);
                }
                else
                {
                    startat++;
                }
                goto Label_00E3;
            Label_00C7:
                startat = match.Index;
                sql = RemoveBlockComments(sql, startat);
                startat++;
            Label_00E3:
                if (startat >= sql.Length)
                {
                    return sql;
                }
                match = _delimiterStart.Match(sql, startat);
                num++;
                if (num > 0x2710)
                {
                    string str2 = Assembly.GetExecutingAssembly().GetName().Version.ToString();
                    throw new SqlParsingException(("Infinite loop in FixComments (" + str2 + ")" + Environment.NewLine + Environment.NewLine) + str);
                }
            }
            return sql;
        }

        public static string NormalizeSqlText(string sql, string eventClass, int spid, bool spreadCsv)
        {
            Match match;
            if (sql == null)
            {
                return "";
            }
            bool flag = false;
            bool flag2 = false;
            int num = 0;
            string key = "";
            if (sql == "sp_reset_connection")
            {
                return "";
            }
            sql = FixComments(sql);
            sql = _spaces.Replace(sql, " ").ToUpper(CultureInfo.InvariantCulture);
            if ((eventClass == "RPC:Completed") || (eventClass == "RPC:Started"))
            {
                sql = _doubleApostrophe.Replace(sql, "{STR}");
                match = _prepExecRpc.Match(sql);
                if (match.Success)
                {
                    sql = match.Groups["statement"].ToString();
                }
                match = _prepareSql.Match(sql);
                if (match.Success)
                {
                    if (match.Groups["preptype"].ToString().ToLower() == "sp_prepare")
                    {
                        flag2 = true;
                    }
                    if (match.Groups["stmtnum"].ToString() == "NULL")
                    {
                        num = 0;
                    }
                    else
                    {
                        num = Convert.ToInt32(match.Groups["stmtnum"].ToString());
                    }
                    sql = match.Groups["remaining"].ToString();
                    match = _preppedSqlStatement.Match(sql);
                    if (match.Success)
                    {
                        sql = match.Groups["statement"].ToString();
                        sql = _doubleApostrophe.Replace(sql, "'${string}'");
                    }
                    flag = true;
                }
                match = null;
                match = _execPrepped.Match(sql);
                if (match.Success)
                {
                    num = Convert.ToInt32(match.Groups["stmtnum"].ToString());
                    if (prepSql.ContainsKey(spid.ToString() + "_" + num.ToString()))
                    {
                        return TruncateSql("{PREPARED} " + prepSql[spid.ToString() + "_" + num.ToString()].ToString());
                    }
                }
                match = null;
                match = _execUnprep.Match(sql);
                if (match.Success)
                {
                    num = Convert.ToInt32(match.Groups["stmtnum"].ToString());
                    key = spid.ToString() + "_" + num.ToString();
                    if (prepSql.ContainsKey(key))
                    {
                        sql = prepSql[key].ToString();
                        prepSql.Remove(spid.ToString() + "_" + num.ToString());
                        match = null;
                        return TruncateSql("{UNPREPARING} " + sql);
                    }
                }
                match = null;
            }
            if ((eventClass == "RPC:Completed") || (eventClass == "RPC:Started"))
            {
                match = _cursor.Match(sql);
                if (match.Success)
                {
                    sql = "{CURSOR} " + match.Groups["statement"].ToString();
                    sql = _doubleApostrophe.Replace(sql, "'${string}'");
                }
                match = _cursorPrepExec.Match(sql);
                if (match.Success)
                {
                    sql = "{CURSOR} " + match.Groups["statement"].ToString();
                    sql = _doubleApostrophe.Replace(sql, "'${string}'");
                }
                match = _spExecuteSql.Match(sql);
                if (match.Success)
                {
                    sql = match.Groups["statement"].ToString();
                }
                match = null;
                if (!_brackets.Match(sql).Success)
                {
                    match = _dbAndObjectName.Match(sql);
                    if (match.Success)
                    {
                        sql = match.Groups["object"].ToString();
                    }
                    else
                    {
                        match = _objectName.Match(sql);
                        if (match.Success)
                        {
                            sql = match.Groups["object"].ToString();
                        }
                    }
                    if (((sql == "SP_CURSOR") || (sql == "SP_CURSORFETCH")) || ((sql == "SP_CURSORCLOSE") || (sql == "SP_RESET_CONNECTION")))
                    {
                        match = null;
                        return "";
                    }
                }
                match = null;
            }
            sql = _emptyString.Replace(sql, "{STR}");
            sql = _stringConstant.Replace(sql, "{STR}");
            sql = _unicodeConstant.Replace(sql, "{NSTR}");
            sql = _binaryConstant.Replace(sql, "{BINARY}");
            sql = _numericConstant.Replace(sql, "${prefix}{##}");
            sql = _inClause.Replace(sql, "{IN}");
            if (spreadCsv)
            {
                sql = _spreadCsv.Replace(sql, ", ");
            }
            sql = _spaces.Replace(sql, " ");
            sql = TruncateSql(sql);
            if (flag && (num != 0))
            {
                if (!prepSql.ContainsKey(spid.ToString() + "_" + num.ToString()))
                {
                    prepSql.Add(spid.ToString() + "_" + num.ToString(), sql);
                }
                else
                {
                    prepSql[spid.ToString() + "_" + num.ToString()] = sql;
                }
            }
            match = null;
            if (flag2)
            {
                return TruncateSql("{PREPARING} " + sql);
            }
            if (flag && !flag2)
            {
                return TruncateSql("{PREPARED} " + sql);
            }
            return TruncateSql(sql);
        }

        private static string RemoveBlockComments(string sql, int position)
        {
            StringBuilder builder = new StringBuilder(sql.Length);
            builder.Append(sql.Substring(0, position));
            int num = 0;
            int startIndex = position;
            while (startIndex < (sql.Length - 1))
            {
                string str = sql.Substring(startIndex, 2);
                if (str == null)
                {
                    goto Label_0065;
                }
                if (!(str == "/*"))
                {
                    if (str == "*/")
                    {
                        goto Label_0057;
                    }
                    goto Label_0065;
                }
                num++;
                startIndex++;
                startIndex++;
                goto Label_0069;
            Label_0057:
                num--;
                startIndex++;
                startIndex++;
                goto Label_0069;
            Label_0065:
                startIndex++;
            Label_0069:
                if (num == 0)
                {
                    if (startIndex < sql.Length)
                    {
                        builder.Append(sql.Substring(startIndex, sql.Length - startIndex));
                    }
                    return builder.ToString();
                }
            }
            return sql;
        }

        private static string TruncateSql(string sql)
        {
            sql = sql.Trim();
            if (sql.Length > 0xfa0)
            {
                return sql.Substring(0, 0xfa0);
            }
            return sql;
        }

    }
}
